<?php 
	$cmb_Mes=$_POST['txtMes'];
	$cmb_Agno = $_POST['txtAgno'];
	$cmb_Proveedor = $_POST['txtCliente'];
	header('Content-type: application/vnd.ms-excel');
	header("Content-Disposition: attachment; filename=LibroFacturaCobrar.xls");
	header("Pragma: no-cache");
	header("Expires: 0");
	require_once("../conexion/conexion.php");
	require_once("../Funciones/funciones_publicas.php");
	$fun = new funciones_publicas();
	$mes = $fun->SeleccionaMes($cmb_Mes);
?>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1" />
<title>Documento sin t&iacute;tulo</title>
</head>

<body>
<?php
extract($_POST);
	//cliente
 	if ($cmb_Proveedor=='0')
	{
		$cmb_Proveedor="null";
	}
	
	//mes
	if ($cmb_Mes=='0')
	{
		$cmb_Mes="null";
	}
	else
	{
		$mes = $fun->SeleccionaMes($cmb_Mes);
	}
	
	//agno
	if ($cmb_Agno==0)
	{
		$cmb_Agno="null";
	}

//factura psmb
$con = new Conexion();
$con2 = $con->conexionBD("fpproduccion");
$sql = "select 
		date_format(facfecha,'%d/%m/%Y') as fecha,
		faccod,
		clirsocial,
		factura.clirut,
		facneto,
		faciva,
		factotal,
		facestado,
		ConVentaDescrip
		
		from factura
		inner join clientes on
		clientes.clirut=factura.clirut
		inner join condicionventa on
		condicionventa.ConVentaCod=factura.ConVentaCod
		where 
		(('$cmb_Proveedor' is NULL) or (factura.CliRut = '".$cmb_Proveedor."')) and
		(($cmb_Mes is NULL) or (month(facfecha) = '".$cmb_Mes."')) and
		(($cmb_Agno is NULL) or (year(facfecha) = '".$cmb_Agno."')) and
		(facestado!='ANULADA' and facestado='NO PAGADO')
		order by facfecha asc";
		
$resultado = mysql_query($sql) or die ($con->error_mysql("Error en la consulta".mysql_error()));
$fila = mysql_num_rows($resultado);
$con->cerrarBD();
//factura dia
$con = new Conexion();
$con2 = $con->conexionBD("fpproduccion");
$sql = "select 
		date_format(FacDiaFecha,'%d/%m/%Y') as fecha,
		FacDiaNumFac,
		clirsocial,
		facturadia.CliRut,
		FacDiaNeto,
		FacDiaIVA,
		FacDiaMonto,
		ConVentaDescrip
		
		from facturadia
		inner join clientes on
		clientes.CliRut=facturadia.CliRut
		inner join condicionventa on
		condicionventa.ConVentaCod=facturadia.ConVentaCod
		where 
		(($cmb_Proveedor is NULL) or (facturadia.CliRut = '".$cmb_Proveedor."')) and
		(($cmb_Mes is NULL) or (month(FacDiaFecha) = '".$cmb_Mes."')) and
		(($cmb_Agno is NULL) or (year(FacDiaFecha) = '".$cmb_Agno."')) and
		(FacDiaEstado!='ANULADA' and FacDiaEstado='NO PAGADO')
		order by FacDiaFecha asc";
		
$resultado2 = mysql_query($sql) or die ($con->error_mysql("Error en la consulta".mysql_error()));
$fila2 = mysql_num_rows($resultado2);
$con->cerrarBD();
//factura infa
$con = new Conexion();
$con2 = $con->conexionBD("fpproduccion");
$sql = "select 
		date_format(FacInfaFecha,'%d/%m/%Y') as fecha,
		FacInfaNumFac,
		clirsocial,
		facturainfa.CliRut,
		FacInfaNeto,
		FacInfaIVA,
		FacInfaMonto,
		ConVentaDescrip
		
		from facturainfa
		inner join clientes on
		clientes.CliRut=facturainfa.CliRut
		inner join condicionventa on
		condicionventa.ConVentaCod=facturainfa.ConVentaCod
		where 
		(($cmb_Proveedor is NULL) or (facturainfa.CliRut = '".$cmb_Proveedor."')) and
		(($cmb_Mes is NULL) or (month(FacInfaFecha) = '".$cmb_Mes."')) and
		(($cmb_Agno is NULL) or (year(FacInfaFecha) = '".$cmb_Agno."')) and
		(FacInfaEstado!='ANULADA' and FacInfaEstado='NO PAGADO')
		order by FacInfaFecha asc";
		
$resultado3 = mysql_query($sql) or die ($con->error_mysql("Error en la consulta".mysql_error()));
$fila3 = mysql_num_rows($resultado3);
$con->cerrarBD();
//factura otros
$con = new Conexion();
$con2 = $con->conexionBD("fpproduccion");
$sql = "select 
		date_format(FacOtrosFecha,'%d/%m/%Y') as fecha,
		FacOtrosNumFac,
		oclirsocial,
		facturaotros.OCliRut,
		FacOtrosNeto,
		FacOtrosIVA,
		FacOtrosMonto,
		ConVentaDescrip
		
		from facturaotros
		inner join otrosclientes on
		otrosclientes.OCliRut=facturaotros.OCliRut
		inner join condicionventa on
		condicionventa.ConVentaCod=facturaotros.ConVentaCod
		where 
		(($cmb_Proveedor is NULL) or (facturaotros.OCliRut = '".$cmb_Proveedor."')) and
		(($cmb_Mes is NULL) or (month(FacOtrosFecha) = '".$cmb_Mes."')) and
		(($cmb_Agno is NULL) or (year(FacOtrosFecha) = '".$cmb_Agno."')) and
		(FacOtrosEstado!='ANULADA' and FacOtrosEstado='NO PAGADO')
		order by FacOtrosFecha asc";
		
$resultado4 = mysql_query($sql) or die ($con->error_mysql("Error en la consulta".mysql_error()));
$fila4 = mysql_num_rows($resultado4);
$con->cerrarBD();

//factura exenta
$con = new Conexion();
$con2 = $con->conexionBD("fpproduccion");
$sql = "select 
		date_format(FECliFecha,'%d/%m/%Y') as fecha,
		FECliNum,
		oclirsocial,
		fe.OCliRut,
		FECliEstado,
		FECliTotal,
		FECliPago
		
		from facexecliente fe
		inner join otrosclientes oc on
		oc.OCliRut=fe.OCliRut
		where
		(($cmb_Proveedor is NULL) or (fe.OCliRut = '".$cmb_Proveedor."')) and
		(($cmb_Mes is NULL) or (month(FECliFecha) = '".$cmb_Mes."')) and
		(($cmb_Agno is NULL) or (year(FECliFecha) = '".$cmb_Agno."')) and
		(FECliEstado!='ANULADA' and FECliEstado='FACTURADO')
		order by FECliFecha asc";
$resultado5 = mysql_query($sql) or die ($con->error_mysql("Error en la consulta".mysql_error()));
$fila5 = mysql_num_rows($resultado5);
$con->cerrarBD();
if(($fila >0)or($fila2>0)or($fila3>0)or($fila4>0)or($fila5>0))
{
?>
<table width="135%" border="1" cellpadding="0" cellspacing="0" >
<tr>
<td colspan="9" bgcolor="#CCCCCC"><div align="center" style="font-size:24px"><strong>Libro de Facturas por Cobrar.</strong></div></td>
</tr>
<tr>
<td colspan="9">&nbsp;</td>
</tr>
<tr>
	<th width="12%" bgcolor="#CCCCCC">FECHA</th>
	<th width="12%" bgcolor="#CCCCCC">FACTURA</th>
	<th width="13%" bgcolor="#CCCCCC">TIPO</th>
	<th width="26%" bgcolor="#CCCCCC">CLIENTE</th>
	<th width="12%" bgcolor="#CCCCCC">RUT</th>
	<th bgcolor="#CCCCCC">FORMA PAGO</th>
	<th width="8%" bgcolor="#CCCCCC">NETO</th>
	<th width="8%" bgcolor="#CCCCCC">IVA</th>
	<th width="9%" bgcolor="#CCCCCC">TOTAL</th>
</tr>
<?php
$sumatoria = 0;
$sumaIVA = 0;
$sumaNeto = 0;
//factura psmb
while($reg = mysql_fetch_array($resultado))
{
	echo "<tr>";
	echo "<td width=\"12%\">".$reg['fecha']."</td>";
	echo "<td width=\"12%\">".$reg['faccod']."</td>";
	echo "<td width=\"13%\">PSMB</td>";
	echo "<td width=\"26%\">".$reg['clirsocial']."</td>";
	echo "<td width=\"12%\">".$reg['clirut']."</td>";
	echo "<td width=\"12%\">".$reg['ConVentaDescrip']."</td>";
	echo "<td width=\"8%\">".number_format ($reg['facneto'],0,',','.')."</td>";
	echo "<td width=\"8%\">".number_format ($reg['faciva'],0,',','.')."</td>";
	echo "<td width=\"9%\">".number_format ($reg['factotal'],0,',','.')."</td>";
	echo "</tr>";
	$sumatoria += $reg['factotal'];
	$sumaIVA += $reg['faciva'];
	$sumaNeto += $reg['facneto'];
}
//factura dia
while($reg = mysql_fetch_array($resultado2))
{		
	echo "<tr>";
	echo "<td width=\"12%\">".$reg['fecha']."</td>";
	echo "<td width=\"12%\">".$reg['FacDiaNumFac']."</td>";
	echo "<td width=\"13%\">DIA</td>";
	echo "<td width=\"26%\">".$reg['clirsocial']."</td>";
	echo "<td width=\"12%\">".$reg['CliRut']."</td>";
	echo "<td width=\"12%\">".$reg['ConVentaDescrip']."</td>";
	echo "<td width=\"8%\">".number_format ($reg['FacDiaNeto'],0,',','.')."</td>";
	echo "<td width=\"8%\">".number_format ($reg['FacDiaIVA'],0,',','.')."</td>";
	echo "<td width=\"9%\">".number_format ($reg['FacDiaMonto'],0,',','.')."</td>";
	echo "</tr>";
	$sumatoria += $reg['FacDiaMonto'];
	$sumaIVA += $reg['FacDiaIVA'];
	$sumaNeto += $reg['FacDiaNeto'];
}
//factura infa
while($reg = mysql_fetch_array($resultado3))
{
	echo "<tr>";
	echo "<td width=\"12%\">".$reg['fecha']."</td>";
	echo "<td width=\"12%\">".$reg['FacInfaNumFac']."</td>";
	echo "<td width=\"13%\">INFA</td>";
	echo "<td width=\"26%\">".$reg['clirsocial']."</td>";
	echo "<td width=\"12%\">".$reg['CliRut']."</td>";
	echo "<td width=\"12%\">".$reg['ConVentaDescrip']."</td>";
	echo "<td width=\"8%\">".number_format ($reg['FacInfaNeto'],0,',','.')."</td>";
	echo "<td width=\"8%\">".number_format ($reg['FacInfaIVA'],0,',','.')."</td>";
	echo "<td width=\"9%\">".number_format ($reg['FacInfaMonto'],0,',','.')."</td>";
	echo "</tr>";
	$sumatoria += $reg['FacInfaMonto'];
	$sumaIVA += $reg['FacInfaIVA'];
	$sumaNeto += $reg['FacInfaNeto'];
}
//factura otros
while($reg = mysql_fetch_array($resultado4))
{
	echo "<tr>";
	echo "<td width=\"12%\">".$reg['fecha']."</td>";
	echo "<td width=\"12%\">".$reg['FacOtrosNumFac']."</td>";
	echo "<td width=\"13%\">OTROS</td>";
	echo "<td width=\"26%\">".$reg['oclirsocial']."</td>";
	echo "<td width=\"12%\">".$reg['OCliRut']."</td>";
	echo "<td width=\"12%\">".$reg['ConVentaDescrip']."</td>";
	echo "<td width=\"8%\">".number_format ($reg['FacOtrosNeto'],0,',','.')."</td>";
	echo "<td width=\"8%\">".number_format ($reg['FacOtrosIVA'],0,',','.')."</td>";
	echo "<td width=\"9%\">".number_format ($reg['FacOtrosMonto'],0,',','.')."</td>";
	echo "</tr>";
	$sumatoria += $reg['FacOtrosMonto'];
	$sumaIVA += $reg['FacOtrosIVA'];
	$sumaNeto += $reg['FacOtrosNeto'];
}

//factura exenta
while($reg = mysql_fetch_array($resultado5))
{
	echo "<tr>";
	echo "<td width=\"12%\">".$reg['fecha']."</td>";
	echo "<td width=\"12%\">".$reg['FECliNum']."</td>";
	echo "<td width=\"13%\">EXENTA</td>";
	echo "<td width=\"26%\">".$reg['oclirsocial']."</td>";
	echo "<td width=\"12%\">".$reg['OCliRut']."</td>";
	echo "<td width=\"12%\">".$reg['FECliPago']."</td>";
	echo "<td width=\"8%\">".number_format ($reg['FECliTotal'],0,',','.')."</td>";
	echo "<td width=\"8%\">0</td>";
	echo "<td width=\"9%\">".number_format ($reg['FECliTotal'],0,',','.')."</td>";
	echo "</tr>";
	$sumatoria += $reg['FECliTotal'];
	$sumaNeto += $reg['FECliTotal'];
}
?>
<tr>
	<td colspan="6"><strong>TOTAL</strong></td>
	<td><strong><?php echo number_format ($sumaNeto,0,',','.')?></strong></td>
	<td><strong><?php echo number_format ($sumaIVA,0,',','.')?></strong></td>
	<td><strong><?php echo number_format ($sumatoria,0,',','.')?></strong></td>
</tr>
</table>
<?php 
}
?>
</body>
</html>
